package com.lagou.module.dao;

import com.lagou.module.entity.PageBean;
import com.lagou.module.entity.Student;
import com.lagou.module.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Date;
import java.sql.SQLException;
import java.util.List;

public class StudentDaoImpl implements StudentDao {

    // show all Student
    @Override
    public List<Student> studentShow(PageBean pageBean) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        StringBuilder stringBuilder = new StringBuilder(" select * from t_student ");
        if (pageBean != null){
            stringBuilder.append(" limit " + pageBean.getStart() + "," + pageBean.getRow() );
        }

        //QueryRunner 不能直接用  StringBuilder 对象   需要先转化为String类型对象
        String sql = stringBuilder.toString();

        try {
            List<Student> students = queryRunner.query(sql, new BeanListHandler<Student>(Student.class));
            return students;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //  add a student
    @Override
    public int studentAdd(Student student) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        String sql = " insert into t_student values (null, ?, ?, ?, ?, ?, ?) ";

        try {
            int i = queryRunner.update(sql, student.getName(), student.getSex(), new Date(student.getBirthday().getTime()), student.getEmail(), student.getComments(), student.getClassGradeId());
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // delete student
    @Override
    public int studentDelete(String[] ids) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        StringBuilder sb = new StringBuilder();
        for (int i = 0; i< ids.length; i++){
            if(i == ids.length-1){
                sb.append(ids[i]);
            }else {
                sb.append(ids[i]).append(",");
            }
        }
        System.out.println(" sb =  " + sb );

        String sql = " delete from t_student where id in ("+ sb + ") ";

        try {
            int i = queryRunner.update(sql);
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // modify a student
    @Override
    public int studentModify(Student student) {

        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        String sql = " update t_student set name = ?, sex = ?, birthday=?, email=?, comments=?, classGradeid =? where id = ? ";

        try {
            int i = queryRunner.update(sql, student.getName(), student.getSex(), student.getBirthday(), student.getEmail(), student.getComments(), student.getClassGradeId(), student.getId());
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // find a student
    @Override
    public Student studentFind(int id) {

        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        String sql = " select * from t_student where id = ? ";

        try {
            Student student = queryRunner.query(sql, new BeanHandler<Student>(Student.class), id);
            return student;
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return null;
    }


    // 通过教室id 来查找学生
    @Override
    public Student studentFindByClassId(int classId) {


        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        String sql = " select * from t_student where classGradeId = ? ";

        try {
            Student student = queryRunner.query(sql, new BeanHandler<Student>(Student.class), classId);
            return student;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }
}
